package DBLayer;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import ModelLayer.City;

public class DBCity implements IFDBCity{
	private Connection con;
	public DBCity(){
		con = DBConnection.getInstance().getDBcon();
	}
	public City findCityByPostCode(int codeZip) {
		String wClause = "codeZip = " + codeZip;
        System.out.println("Search City(by code): " + wClause);
        return singleWhere(wClause);
	}

	public City findCityByName(String cityName) {
		String wClause = "city = '" + cityName+"'";
        System.out.println("Search City(by name): " + wClause);
        return singleWhere(wClause);
	}

	public void insertNewCity(City city){
		 String query="INSERT INTO City(codeZip, city)  VALUES("+
			  		city.getCodeZip()  + ",'"  +
				    	city.getCity()  + "')";
		                     
		 System.out.println("insert : " + query);		  					
		    try{
		        Statement stmt = con.createStatement();
			 	stmt.setQueryTimeout(5);
			 	stmt.executeUpdate(query);
			 	stmt.close();
				}
			 	catch(SQLException ex){
			 	  	System.out.println("Insert exception in city db: "+ex);
			 	  	ex.printStackTrace();
			   }
	}

	public void updateCity(City city) {
		City cityObj = city;
		String query = "Update City set " +
				"city = '"+cityObj.getCity()+"'," +
				"where codeZip = "+cityObj.getCodeZip();
		System.out.println("Update query: "+query);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			stmt.close();
		}catch(SQLException sql){
			System.out.println("SQLException update query in city: "+sql);
		}
	}
	private City singleWhere(String wClause)
	{
		ResultSet results;
		City cityObj = new City();
	    String query =  buildQuery(wClause);
        System.out.println(query);
        
		try{
			
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
	 			cityObj = buildCity(results);
                stmt.close();
			}
		
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return cityObj;
	}
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM City";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	private City buildCity(ResultSet results){   
		City cityObj = new City();
        try{
        	cityObj.setCodeZip(results.getInt(1));
        	cityObj.setCity(results.getString(2));
        }
       catch(Exception e)
       {
           System.out.println("error in building the customer object");
       }
       return cityObj;
    }
}